from sqlalchemy import Table, Column, Sequence, ForeignKey
from sqlalchemy import BigInteger, Date, Integer, String, Boolean
from sqlalchemy import UniqueConstraint

# TODO: add version informations where needed
def update(metadata):
##### ITEM TABLES #############################################################
    # Products organization is tracking
    #
    # name - descriptive name of product
    # serializable - does product have serial number
    # uom_fk - pointer to unit of measure
    Table('m01_p', metadata,
        Column('id', BigInteger, Sequence('p_s'), primary_key=True),
        Column('name', String(100)),
        Column('serializable', Boolean, nullable=False),
        Column('uom_id', Integer, ForeignKey('m01_uom.id')))

    # Item - aka item instance
    #
    # p_id - pointer to product
    # sku - stock keeping unit
    # sn - serial number
    # va_id - pointer to variant
    #
    # TODO: Maybe we can factor out sn into separate table
    # (IDs should be in separate 1:1 table. But joining 3 tables is decreasing
    # query performance.)
    Table('m01_i', metadata,
        Column('id', BigInteger, Sequence('i_s'), primary_key=True),
        Column('p_id', BigInteger, ForeignKey('m01_p.id')),
        Column('sku', String(255), nullable=False),
        Column('sn', String(255), nullable=True),
        Column('va_id', BigInteger, ForeignKey('m01_p_va.id')))

    # Unite Of Measure
    #
    # Enum of: kg, m, pcs
    # desc_i18n - i18n description of attribute
    Table('m01_uom', metadata,
        Column('id', Integer, Sequence('generic_s'), primary_key=True),
        Column('desc_i18n', String(40), nullable=False),
        UniqueConstraint('desc_i18n'))

##### PRODUCT IDENTIFICATION TABLES ###########################################
    # Supplier sku
    #
    # i_id - pointer to item idc
    # s_id - supplier identifier
    # sku - stock keeping unit
    Table('m01_i_ssku', metadata,
        Column('i_id', BigInteger, ForeignKey('m01_i.id'),
               nullable=False),
        Column('s_id', Integer, ForeignKey('m01_s.id'), nullable=False),
        Column('sku', String(255), nullable=False))

    # Item dynamic id codes
    #
    # i_id - pointer to item id codes
    # i_didc_a_id - pointer to dynamic code attribute name
    # didc_av - dynamic id code value
    Table('m01_i_didc_av', metadata,
        Column('i_id', BigInteger, ForeignKey('m01_i.id')),
        Column('i_didc_a_id', Integer, ForeignKey('m01_i_didc_a.id'),
               nullable=False),
        Column('didc_av', String(255), nullable=False))

    # Dynamic item id code attributes
    #
    # desc_i18n - i18n description of attribute
    Table('m01_i_didc_a', metadata,
        Column('id', Integer, Sequence('generic_s'), primary_key=True),
        Column('desc_i18n', String(255), nullable=False))

#### VARIANT TABLES ###########################################################
    # Product variant
    #
    # desc_i18n - i18n variant name
    Table('m01_p_va', metadata,
        Column('id', BigInteger, Sequence('m01_p_va_s'), primary_key=True),
        Column('desc_i18n', String(100)))

    # Product variant option
    #
    # desc_i18n - i18n option name
    Table('m01_p_va_o', metadata,
        Column('id', Integer, Sequence('generic_s'), primary_key=True),
        Column('desc_i18n', String(100)))

    # Variant option value
    #
    # p_va_o_id - pointer to variant option
    # desc_i18n - i18n variant option value
    Table('m01_p_va_ov', metadata,
        Column('id', Integer, Sequence('generic_s'), primary_key=True),
        Column('p_va_o_id', Integer, ForeignKey('m01_p_va_o.id')),
        Column('desc_i18n', String(100)))

    # Variant definition
    # (What option and value variant consists of)
    #
    # p_va_id - pointer to variant
    # p_ov_id - pointer to option value
    Table('m01_p_va_d', metadata,
        Column('p_va_id', BigInteger, ForeignKey('m01_p_va.id')),
        Column('p_va_ov_id', BigInteger, ForeignKey('m01_p_va_ov.id')))

#### INVENTORY ITEM TABLES ####################################################
    # Warehouse definitions within organization
    #
    # desc - description of warehouse
    Table('m01_whs', metadata,
        Column('id', Integer, Sequence('generic_s'), primary_key=True),
        Column('desc', String(100), nullable=False),
        UniqueConstraint('desc'))

    # Inventory item - aka item on warehouse
    #
    # i_id - pointer to item
    # whs_id - pointer to warehouse
    # qty - quantity on hand
    Table('m01_ii', metadata,
        Column('i_id', BigInteger, ForeignKey('m01_i.id')),
        Column('whs_id', BigInteger, ForeignKey('m01_whs.id')),
        Column('qty', Integer, nullable=True),
        UniqueConstraint('i_id', 'whs_id'))

    # Stock level quantities
    # Snapshot of stocklevel in time
    #
    # i_id - pointer to item
    # whs_id - pointer to warehouse
    # date_ob - openning balance date
    # qty_ob - quantity opening balance
    # qty_iss - quantity issued
    # qty_rec - quantity received
    # qty_adj - quantity adjusted
    Table('m01_slq', metadata,
        Column('id', BigInteger, Sequence('stock_levels_s'), primary_key=True),
        Column('i_id', BigInteger, ForeignKey('m01_i.id'), nullable=False),
        Column('whs_id', Integer, ForeignKey('m01_whs.id'), nullable=False),
        Column('date_ob', Date, nullable=False),
        Column('qty_ob', Integer, nullable=False),
        Column('qty_iss', Integer, nullable=False),
        Column('qty_rec', Integer, nullable=False),
        Column('qty_adj', Integer, nullable=False))

##### BUSINESS PARTNER TABLES #################################################
    # Business Partner
    #
    # desc - description of business partner
    Table('m01_bp', metadata,
        Column('id', Integer, Sequence('generic_s'), primary_key=True),
        Column('desc', String(100), nullable=False),
        UniqueConstraint('desc'))

    # Supplier
    #
    # id - pointer to business partner
    # bp_id - id of bussiness partner
    Table('m01_s', metadata,
        Column('id', Integer, Sequence('generic_s'), primary_key=True),
        Column('bp_id', Integer, ForeignKey('m01_bp.id'), nullable=False),
        UniqueConstraint('bp_id'))

    # Consumer
    #
    # id - pointer to business partner
    # bp_id - id of bussiness partner
    Table('m01_cust', metadata,
        Column('id', Integer, Sequence('generic_s'), primary_key=True),
        Column('bp_id', Integer, ForeignKey('m01_bp.id'), nullable=False),
        UniqueConstraint('bp_id'))

##### ADDRESS TABLES ##########################################################
    # Address
    #
    # self descriptive
    Table('m01_add', metadata,
        Column('id', Integer, Sequence('generic_s'), primary_key=True),
        Column('line_1', String(50, convert_unicode=True), nullable=False),
        Column('line_2', String(50), nullable=True),
        Column('line_3', String(50), nullable=True),
        Column('city', String(50), nullable=False),
        Column('zip', String(10), nullable=False),
        Column('state', String(50), nullable=True),
        Column('country', String(50), nullable=False))

    # Address type
    #
    # Enum of: office, warehouse etc ...
    # desc_i18n - i18n option value
    Table('m01_add_type', metadata,
        Column('id', Integer, Sequence('generic_s'), primary_key=True),
        Column('desc_i18n', String(40), nullable=False),
        UniqueConstraint('desc_i18n'))

    # Warehouse address
    #
    # whs_id - pointer to warehouse
    # add_id - pointer to address
    # add_type_id - pointer to address type
    # note - note of address warehouse association
    Table('m01_whs_add', metadata,
        Column('id', Integer, Sequence('generic_s'), primary_key=True),
        Column('whs_id', Integer, ForeignKey('m01_whs.id'), nullable=False),
        Column('add_id', Integer, ForeignKey('m01_add.id'), nullable=False),
        Column('add_type_id', Integer, ForeignKey('m01_add_type.id')),
        Column('note', String(200), nullable=True))

    # Business partner address
    #
    # bp_id - pointer to bussiness partner
    # add_id - pointer to address
    # add_type_id - pointer to address type
    # note - note of address business partner association
    Table('m01_bp_add', metadata,
        Column('id', Integer, Sequence('generic_s'), primary_key=True),
        Column('bp_id', Integer, ForeignKey('m01_bp.id'), nullable=False),
        Column('add_id', Integer, ForeignKey('m01_add.id'), nullable=False),
        Column('add_type_id', Integer, ForeignKey('m01_add_type.id')),
        Column('note', String(200), nullable=True))

##### MEDIA TYPES #############################################################
    # Media Types
    #
    # Enum of: email, phone etc ...
    # desc_i18n - i18n option value
    Table('m01_med_type', metadata,
        Column('id', Integer, Sequence('generic_s'), primary_key=True),
        Column('desc_i18n', String(40), nullable=False),
        UniqueConstraint('desc_i18n'))

    # Media contact
    #
    # Array of defined concrete media contact. Phone, email addresses, skype
    #
    # con_str - media connection
    Table('m01_med_con', metadata,
        Column('id', Integer, Sequence('generic_s'), primary_key=True),
        Column('con_str', String(40), nullable=False))

    # Media Types
    #
    # Association of business partne, media type, media contact
    #
    # bp_id - pointer to bussiness partner
    # med_con_id - pointer to media contact
    # med-id - pointer to media contact type
    # note - note for association
    Table('m01_bp_med_con', metadata,
        Column('id', Integer, Sequence('generic_s'), primary_key=True),
        Column('bp_id', Integer, ForeignKey('m01_bp.id'), nullable=False),
        Column('med_con_id', Integer, ForeignKey('m01_med_con.id'),
               nullable=False),
        Column('med_type_id', Integer, ForeignKey('m01_med_type.id'),
               nullable=False),
        Column('note', String(200), nullable=True))
